package com.bct.jxc.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.PropertyResourceBundle;

import javax.swing.JOptionPane;

import com.bct.jxc.common.Constants;
import com.bct.jxc.common.ErrorManager;
import com.bct.jxc.common.StringUtil;

/**
 * 数据库访问工具类，进行数据库的底层操作，配置信息在SqlConfig.properties文件中
 * @author ASUS
 *
 */
public class SqlManager {
	private static SqlManager manager = null;//静态成员变量，支持单例模式
	private static PropertyResourceBundle bundle = null;//资源文件
	private static String jdbcDriver = "";//JDBC驱动类
	private String DBhost = "";//数据库主机名
	private String DBname = "";//数据库名
	private String DBport = "";//数据库端口
	private String DBuser = "";//数据库用户名
	private String DBpassword = "";//数据库密码
	private String strcon = null;//连接字符串
	
	private Connection connection = null;// 连接对象
	private PreparedStatement pstmt = null;// 预处理接口
	private CallableStatement cstm = null;//过程函数接口
	/**
	 * 私有，只能在本类中调用，不可实例化
	 */
	private SqlManager() {
		try {
			//读取数据库配置文件
			bundle = new PropertyResourceBundle(SqlManager.class.getResourceAsStream("SqlConfig.properties"));
			this.DBhost = getString("DBhost");
			this.DBname = getString("DBname");
			this.DBport = getString("DBport");
			this.DBuser = getString("DBuser");
			this.DBpassword = getString("DBpassword");
			String database_type = getString("database-type");//读取数据库类型
			if(!StringUtil.isEmpty(database_type)) {//如果类型不为空
				if(database_type.toLowerCase().equals("mysql")) {//mysql数据库连接
					jdbcDriver = "com.mysql.jdbc.Driver";
					strcon = "jdbc:mysql://"+DBhost+":"+DBport+"/"+DBname;
				}else if(database_type.toLowerCase().equals("oracle")) {//oracle数据库连接
					jdbcDriver = "oracle.jdbc.OracleDriver";
					strcon = "jdbc:oracle:thin:@"+DBhost+":"+DBport+":"+DBname;
				}else if(database_type.toLowerCase().equals("sqlserver2005")) {//sqlserver2005数据库连接
					jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
					strcon = "jdbc:sqlserver://"+DBhost+":"+DBport+";+DatabaseName="+DBname;
				}
			}
		} catch (IOException e) {
			ErrorManager.printError("SqlManager 构造方法", e);
		}
	}
	/**
	 * 读取配置文件的值
	 * @param key 配置文件的key
	 * @return
	 */
	private String getString(String key) {
		return this.bundle.getString(key);
	}
	/**
	 * 单例模式获取实例
	 * @return
	 */
	public static SqlManager createInstance() {
		if(manager == null) {
			manager = new SqlManager();
			manager.initDB();
		}
		return manager;
	}
	/**
	 * 初始化数据库连接参数
	 */
	public void initDB() {
		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			ErrorManager.printError("SqlManager initDB()", e);
		}
		System.out.println("成功加载驱动类！");
	}
	/**
	 * 连接数据库
	 */
	public void connectDB() {
		try {
			connection = DriverManager.getConnection(strcon,DBuser,DBpassword);
			connection.setAutoCommit(false);// 默认非自动提交
		} catch (SQLException e) {
			if (e.getErrorCode() == 0) {
				JOptionPane.showMessageDialog(null, "数据库未启动，请检查配置是否正确！", "警告", JOptionPane.WARNING_MESSAGE);
			}
			if (e.getErrorCode() == 1045) {
				JOptionPane.showMessageDialog(null, "数据库连接错误，请检查用户名或密码是否正确！", "警告", JOptionPane.WARNING_MESSAGE);
			}
			if (e.getErrorCode() == 1049) {
				JOptionPane.showMessageDialog(null, "数据库名称错误，请检查数据库名称是否正确！", "警告", JOptionPane.WARNING_MESSAGE);
			}
			ErrorManager.printError("connectDB() error", e);
		}
		System.out.println("成功连接到数据库！");
	}
	/**
	 * 关闭数据库
	 */
	public void closeDB() {
		try {
			if(pstmt != null) {
				pstmt.close();
			}
			if(cstm != null) {
				cstm.close();
			}
			if(connection != null) {
				connection.close();
			}
			
		} catch (Exception e) {
			ErrorManager.printError("closeDB() error", e);
		}
		finally {
			pstmt = null;
			cstm = null;
			connection = null;
		}
		System.out.println("成功关闭数据库资源！");
	}
	/**
	 * 设置PrepareStatemant对象中的sql语句中的参数
	 * @param sql
	 * @param params
	 */
	private void setPrepareStatemantParams(String sql,Object[] params) {
		try {
			pstmt = connection.prepareStatement(sql);//获取预处理对象
			if(params != null) {
				for(int i = 0;i < params.length;i++) {
					pstmt.setObject(i+1, params[i]);
				}
			}
		} catch (SQLException e) {
			ErrorManager.printError("SqlManager setPrepareStatemantParams() error", e);
		}	
	}
	/**
	 * 设置CallableStatement对象中的sql语句中的参数
	 * @param sql
	 * @param params
	 */
	private void setCallableStatementParams(String sql,Object[] params) {
		try {
			cstm = connection.prepareCall(sql);
			if(params != null) {
				for (int i = 0; i < params.length; i++) {
					cstm.setObject(i+1, params[i]);
				}
			}
		} catch (SQLException e) {
			ErrorManager.printError("SqlManager setCallableStatementParams() error", e);
		}
	}
	/**
	 * 执行查询
	 * @param sql
	 * @param params
	 * @param type
	 * @return
	 */
	public ResultSet executeQuery(String sql,Object[] params,int type) {
		ResultSet rs = null;
		try {
			switch (type) {
			case Constants.PSTM_TYPE://预处理
				manager.setPrepareStatemantParams(sql, params);
				rs= pstmt.executeQuery();
				break;
			case Constants.CALL_TYPE://过程函数
				manager.setCallableStatementParams(sql, params);
				rs = cstm.executeQuery();
				break;
			default:
				throw new Exception("不存在该选项！");
			}
		}catch(Exception e) {
			ErrorManager.printError("SqlManager executeQuery()", e);
		}
		return rs;
	}
	/**
	 * 更新数据库操作
	 * @param sql
	 * @param params
	 * @param type
	 * @return
	 */
	public boolean executeUpdate(String sql, Object[] params,int type) {
		boolean res = false;
		try {
			switch (type) {
			case Constants.PSTM_TYPE://预处理
				manager.setPrepareStatemantParams(sql, params);
				pstmt.executeUpdate();
				//提交事务，否则在缓存中
				manager.commitChange();
				res = true;
				break;
			case Constants.CALL_TYPE://过程函数
				manager.setCallableStatementParams(sql, params);
				cstm.executeUpdate();
				//提交事务，否则在缓存中
				manager.commitChange();
				res = true;
				break;
			default:
				throw new Exception("不存在该选项！");
			}
		}catch(Exception e) {
			ErrorManager.printError("SqlManager executeUpdate()", e);
		}
		return res;
	}
	/**
	 * 提交数据到数据库
	 * @throws SQLException
	 */
	private void commitChange() throws SQLException{
		try {
			connection.commit();
			System.out.println("数据提交成功！");
		} catch (Exception e) {
			ErrorManager.printError("SqlManager commitChange()", e);
		}
		
	}
}
